package com.mhkj.execute.plan.repository;

import java.util.List;
import java.util.Map;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mhkj.revise.plan.entity.PlanNode;
import com.mics.core.system.base.repository.BaseRepository;

/**
 * 计划节点
 * 
 * @author mics
 * @date 2018年7月6日
 * @version 1.0
 */
public interface PlanNodeExecutionRepository extends BaseRepository<PlanNode> {


	/**
	 * 根据计划查询计划下所有节点 
	 * @author mics
	 * @date 2018年8月13日
	 * @version 1.0
	 */
	@Query(value="select * from t_plan_node t where t.node_name like CONCAT('%',:nodeName,'%') and date_format(t.begin_date,'%Y-%m')>=:startDate and date_format(t.begin_date,'%Y-%m')<= :endDate  and t.state in (:state) union " + 
			"select * from t_plan_node t where t.node_name like CONCAT('%',:nodeName,'%') and date_format(t.end_date,'%Y-%m')>= :startDate and date_format(t.end_date,'%Y-%m')<= :endDate  and t.state in (:state) limit :pageIndex, :pageSize ",nativeQuery=true)
	List<PlanNode> monthlyPlanListByPage(@Param("nodeName")String nodeName,@Param("startDate")String startDate,@Param("endDate")String endDate, @Param("state")String state, @Param("pageIndex")int pageIndex, @Param("pageSize")int pageSize);
	
	/**
	 * 根据计划查询计划下所有节点 
	 * @author mics
	 * @date 2018年8月13日
	 * @version 1.0
	 */
	@Query(value="select * from t_plan_node t where t.node_name like CONCAT('%',:nodeName,'%') and date_format(t.begin_date,'%Y-%m')>=:startDate and date_format(t.begin_date,'%Y-%m')<= :endDate  and t.state in (:state) union " + 
			"select * from t_plan_node t where t.node_name like CONCAT('%',:nodeName,'%') and date_format(t.end_date,'%Y-%m')>= :startDate and date_format(t.end_date,'%Y-%m')<= :endDate and t.state in (:state)",nativeQuery=true)
	List<PlanNode> monthlyPlanListByPageSize(@Param("nodeName")String nodeName,@Param("startDate")String startDate,@Param("endDate")String endDate,@Param("state")String state);

	
	/**
	 * 根据计划类型查询计划下所有节点 
	 * @author mics
	 * @date 2018年8月13日
	 * @version 1.0
	 */
	@Query(value="select tpn.* from t_plan_node tpn " + 
			"left join t_plan tp on tpn.plan_id=tp.id " + 
			"left join t_dictionary td on tp.plan_type_id=td.id  where td.id= :planType order by begin_date, tpn.id asc",nativeQuery=true)
	List<PlanNode> listByPage(@Param("planType")long planType);
	
	
	/**
	 * 根据计划查询计划下所有的节点统计
	 * @author mics
	 * @date 2018年8月13日
	 * @version 1.0
	 */
	@Query(value="select count(*) AS allTasks, " + 
			"count( CASE WHEN a.state='1' THEN 1 END) AS notStartedTasks, " + 
			"count( CASE WHEN a.state ='2' THEN 1 END) AS Tasksing, " + 
			"count( CASE WHEN a.state = '5' or a.state = '6'  THEN 1 END) AS completeTasks, " + 
			"count( CASE WHEN a.state ='7' THEN 1 END) AS overdueTasks " + 
			"from (select t.* from t_plan_node t  LEFT JOIN t_plan tp on tp.id=t.plan_id "+
			"			inner join t_dictionary d on tp.plan_type_id= d.id  " + 
			"			left outer join t_item i on tp.item_id=i.id "
			+ "where 1=1 and t.begin_date<=:endDate and t.end_date>= :startDate "
			+ "and (i.id in ( :itemId ) or i.id is null) and tp.state =2" + 
			"			and (d.id =223 or d.id =224) and t.post_id in( :postId )" + 
			" ) a ",nativeQuery=true)
	Map<String,Object> workbenchCount(@Param("startDate")String startDate,@Param("endDate")String endDate,@Param("itemId")List<String> itemId,@Param("postId")List<String> postId);
	
	/**
	 * 根据计划类型查询计划下所有节点 
	 * @author mics
	 * @date 2018年8月13日
	 * @version 1.0
	 */
	@Query(value="select fad.id as detailed_id,pn.node_name,pn.id as node_id from t_plan_node pn " + 
			"LEFT JOIN t_plan_feed_back pf on  pf.plan_node_id=pn.id " + 
			"LEFT JOIN t_key_fruit_archives fa on  fa.plan_feed_back_id=pf.id " + 
			"LEFT JOIN t_key_fruit_archives_detailed fad on  fad.key_fruit_archives_id=fa.id " + 
			"where pn.id= :planNodeId ",nativeQuery=true)
	List<Map<String, Object>> dataPrintInfo(@Param("planNodeId")Long planNodeId);
	
	
	


	
}
